package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;

import org.elasticsearch.index.mapper.SourceToParse;
import org.springframework.stereotype.Repository;

import com.x.erp.model.Region;
import com.x.framework.dao.BaseDao;

@Repository
public class RegionDao extends BaseDao {
	private final static String selectRegionListSql = "SELECT "
			+ "T1.*, "
			+ "T2.MARKET_NAME "
			+ "FROM ERP_REGION T1, "
			+ "ERP_MARKET T2, "
			+ "ERP_MARKET T3 "
			+ "WHERE "
			+ "T1.MARKET_ID = T2.MARKET_ID "
    		+ "AND T3.MARKET_ID = ? AND T2.MARKET_CODE LIKE CONCAT(T3.MARKET_CODE, '%') ";
	public List<Region> selectRegionList(Region region) throws Exception{
		StringBuilder sb = new StringBuilder(selectRegionListSql);
		List<Object> params = new ArrayList<Object>();
		params.add(region.getMarketId());
		selectSearch(sb, params, region);
		sb.append("ORDER BY REGION_TYPE ASC, REGION_REST_NUM DESC ");
		return this.queryForList(sb.toString(), Region.class, params, region);
	}


	private final static String selectRegionNumSql = "SELECT "
			+ "COUNT(T1.REGION_ID) "
			+ "FROM ERP_REGION T1, "
			+ "ERP_MARKET T2, "
			+ "ERP_MARKET T3 "
			+ "WHERE "
			+ "T1.MARKET_ID = T2.MARKET_ID "
			+ "AND T3.MARKET_ID = ? AND T2.MARKET_CODE LIKE CONCAT(T3.MARKET_CODE, '%') ";
	public int selectRegionNum(Region region) throws Exception{
		StringBuilder sb = new StringBuilder(selectRegionNumSql);
		List<Object> params = new ArrayList<Object>();
		params.add(region.getMarketId());
		selectSearch(sb, params, region);
		return this.queryForInt(sb.toString(), params);

	}

	private void selectSearch(StringBuilder sb, List<Object> params, Region region) {
		if(this.isString(region.getRegionId())){
			sb.append("AND T1.REGION_ID = ? ");
			params.add(region.getRegionId());
		}
		if(this.isString(region.getRegionParentId())){
			sb.append("AND T1.REGION_PARENT_ID = ? ");
			params.add(region.getRegionParentId());
		}
		if(this.isString(region.getRegionName())){
			sb.append("AND T1.REGION_NAME = ? ");
			params.add(region.getRegionName());
		}
		if(region.getRegionType() != null){
			sb.append("AND T1.REGION_TYPE = ? ");
			params.add(region.getRegionType());
		}
		if (region.getRegionVO() != null){
			if(region.getRegionVO().getMinRegionRestNum() != null){
				sb.append("AND T1.REGION_REST_NUM >= ? ");
				params.add(region.getRegionVO().getMinRegionRestNum());
			}
			if(region.getRegionVO().getMaxRegionRestNum() != null){
				sb.append("AND T1.REGION_REST_NUM <= ? ");
				params.add(region.getRegionVO().getMaxRegionRestNum());
			}
			if(region.getRegionVO().getMinRegionRentFee() != null){
				sb.append("AND T1.REGION_RENT_FEE >= ? ");
				params.add(region.getRegionVO().getMinRegionRentFee());
			}
			if(region.getRegionVO().getMaxRegionRentFee() != null){
				sb.append("AND T1.REGION_RENT_FEE <= ? ");
				params.add(region.getRegionVO().getMaxRegionRentFee());
			}
			if(region.getRegionVO().getIsRest() != null){
				if (region.getRegionVO().getIsRest()){
					sb.append("AND T1.REGION_REST_NUM > 0 ");
				}else{
					sb.append("AND T1.REGION_REST_NUM = 0 ");
				}
			}
		}
	}
}
